/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Util;

import DTO.ChuyenMuc;
import DTO.Xpath;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author THIEUQUANG
 */
public class DataProvider {

    private Connection con = null;

    public static String TCP = null;
    public static String DatabaseName = null;
    public static String User = null;
    public static String password = null;

    public boolean CheckConnection(String host, String db, String user, String pass) {
        try {
            TCP = host;
            DatabaseName = db;
            User = user;
            password = pass;
            
            String connect = "jdbc:sqlserver://localhost:" + TCP + ";databaseName="
                    + DatabaseName + ";user=" + User + ";password=" + password;

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            this.con = DriverManager.getConnection(connect);

            if (con == null) {
                return false;
            }

        } catch (Exception e) {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }
        return true;
    }

    /**
     * Mở kết nối dữ liệu
     *
     */
    public void open() throws ClassNotFoundException, SQLException {
        String sql = "jdbc:sqlserver://localhost:" + TCP + ";databaseName="
                + DatabaseName + ";user=" + User + ";password=" + password;
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        this.con = DriverManager.getConnection(sql);
    }

    /**
     * Đóng kết nối dữ liệu
     *
     */
    public void close() throws SQLException {
        this.con.close();
    }

    /**
     * Rút trích dữ liệu
     *
     * @param sql câu truy vấn SELECT
     * @return ResultSet
     */
    public ResultSet ExecuteQuery(String sql) {
        ResultSet rs = null;
        try {
            Statement sm = this.con.createStatement();
            rs = sm.executeQuery(sql);
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }

        return rs;
    }

    /**
     * Thêm, xóa, cập nhật dữ liệu
     *
     * @param sql câu lệnh UPDATE, INSERT, DELETE
     * @return số lượng dòng cập nhật/thêm/xóa được , -1 : thất bại
     * @exception SQLException
     */
    public int executeUpdate(String sql) {
        int n = -1;
        try {
            Statement sm = this.con.createStatement();
            n = sm.executeUpdate(sql);
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return n;
    }

    /**
     * Gọi store thêm trang mới
     *
     * @param tenTrang
     * @param cm
     * @param x
     * @throws SQLException
     */
    public int CallStore_spThemTrangMoi(String tenTrang, ChuyenMuc cm, Xpath x) throws SQLException {
        CallableStatement statement = con.prepareCall("{call sp_ThemTrangMoi(?,?,?,?,?,?,?,?,?,?)}");

        statement.setString(1, tenTrang);
        statement.setString(2, cm.getTenCM());
        statement.setString(3, cm.getWebLink());
        statement.setString(4, cm.getTimeEnd());
        statement.setString(5, x.getLink());
        statement.setString(6, x.getTitle());
        statement.setString(7, x.getImage());
        statement.setString(8, x.getDescrible());
        statement.setString(9, x.getNext());
        statement.setString(10, x.getTime());

        return statement.executeUpdate();
    }

    public int CallStore_spThemTinMoi(String WebLink, String link, String title, String image, String time, String desc) throws SQLException {
        CallableStatement statement = con.prepareCall("{call sp_ThemTinMoi(?,?,?,?,?,?)}");

        statement.setNString(1, link);
        statement.setNString(2, title);
        statement.setNString(3, image);
        statement.setNString(4, time);
        statement.setNString(5, desc);
        statement.setNString(6, WebLink);

        return statement.executeUpdate();
    }

    /**
     * Lấy tin tức thông qua weblink
     *
     * @param weblink
     * @return
     * @throws SQLException
     */
    public ResultSet CallStore_spLayTinTuc(String weblink) throws SQLException {
        ResultSet rs = null;

        CallableStatement statement = con.prepareCall("{call sp_LayTinTuc(?)}");

        statement.setString(1, weblink);

        rs = statement.executeQuery();

        return rs;
    }

    public int CallStore_spThemWebLink(String WebName, ChuyenMuc cm) throws SQLException {
        CallableStatement statement = con.prepareCall("{call sp_ThemWebLink(?,?,?,?)}");

        statement.setString(1, WebName);
        statement.setNString(2, cm.getTenCM());
        statement.setString(3, cm.getWebLink());
        statement.setString(4, cm.getTimeEnd());

        return statement.executeUpdate();
    }
}
